﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using DIS.Infrastructure.Entities;
using System.Data.SqlClient;
using DIS.Infrastructure.Entities.Entity;
using System.Data;
using System.Text;
using DIS.Infrastructure.Entities.DTO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using DIS.Portal.WebLogic;
using DIS.Infrastructure.Entities.Enum;

namespace IDS.Portal.Web.Controllers
{
    [Auth]
    public class SummaryDataController : Controller
    {
        //
        // GET: /SummaryData/

        public ActionResult Index()
        {
            return View();
        }
        #region 市内交通费

        public ActionResult CityTaxiFeeSummary(int page = 0)
        {
            int count = 0;
            List<CityTaxi> cityTaxiList = null;
            using (Context ctx = new Context())
            {
                string countSql = @"select ROW_NUMBER() OVER(ORDER BY a.Year desc,a.Month desc) as rownumber, a.*,b.ProjectCode,b.ProjectName from (select Year,Month,StartDate,EndDate,ProjectId,SUM(taxiFee) as taxiFee,SUM(invoiceCount) as invoiceCount from dbo.T_CityTaxi
                            where Status=1
                            group by Year,Month,StartDate,EndDate,ProjectId) a
                            inner join dbo.T_Project b on a.ProjectId=b.Id";
                var dataCount = ctx.ExcuteSQL(countSql);
                count = dataCount != null ? dataCount.Rows.Count : 0;
            }

            using (Context ctx = new Context())
            {
                string sql = @"select top (@pagesize) t.* from (select ROW_NUMBER() OVER(ORDER BY a.Year desc,a.Month desc) as rownumber, a.*,b.ProjectCode,b.ProjectName from (select Year,Month,StartDate,EndDate,ProjectId,SUM(taxiFee) as taxiFee,SUM(invoiceCount) as invoiceCount from dbo.T_CityTaxi
                                where Status=1
                                group by Year,Month,StartDate,EndDate,ProjectId) a
                                inner join dbo.T_Project b on a.ProjectId=b.Id) t
                                where t.RowNumber>@pagesize*@page";
                var datatable = ctx.ExcuteSQL(sql,
                         new SqlParameter("@pagesize", 20),
                     new SqlParameter("@page", page)
                    );

                if (datatable != null && datatable.Rows.Count > 0)
                {
                    cityTaxiList = new List<CityTaxi>();
                    foreach (DataRow dr in datatable.Rows)
                    {
                        var cityTaxi = new CityTaxi();
                        cityTaxi.Year = dr["Year"].ToString();
                        cityTaxi.Month = dr["Month"].ToString();
                        cityTaxi.StartDate = dr["StartDate"].ToString();
                        cityTaxi.EndDate = dr["EndDate"].ToString();
                        cityTaxi.ProjectId = int.Parse(dr["ProjectId"].ToString() == "" ? "0" : dr["ProjectId"].ToString());
                        cityTaxi.project = new Project() { ProjectCode = dr["ProjectCode"].ToString(), ProjectName = dr["ProjectName"].ToString() };
                        cityTaxi.TaxiFee = decimal.Parse(dr["taxiFee"].ToString() == "" ? "0" : dr["taxiFee"].ToString());
                        cityTaxi.InvoiceCount = int.Parse(dr["invoiceCount"].ToString() == "" ? "0" : dr["invoiceCount"].ToString());
                        cityTaxiList.Add(cityTaxi);
                    }

                }


            }
            var list = new ItemListBox<CityTaxi>(cityTaxiList).BuildPage(
                  count, page, 20, new PageParameter() { Style = "workspace" });
            ViewBag.feelist = list;
            return View();
        }

        public ActionResult CityTaxiFeeSummaryDetail(string year, string month, int projectId = 0)
        {
            ViewBag.project = new Project().Find(projectId.ToString());
            ViewBag.financialPeriod = new FinancialPeriod().Find(c => c.Year == year && c.Month == month);

            using (Context ctx = new Context())
            {
                var data = (from c in ctx.CityTaxiDetail
                            join d in ctx.CityTaxi
                            on c.CityTaxiId equals d.Id
                            join e in ctx.User
                            on d.UserName equals e.UserName
                            where d.Year == year && d.Month == month && d.ProjectId == projectId
                            orderby d.UserName
                            select new { c, d, e })
                          .ToList()
                          .Select(f => new CityTaxiDetail().Set(f.c).LoadCityTaxi(f.d, f.e)).ToList();
                ViewBag.data = data;

            }

            return View();
        }

        public void CityTaxiFeeExcel(string year, string month, int projectId = 0)
        {
            var project = new Project().Find(projectId.ToString());
            var financialPeriod = new FinancialPeriod().Find(c => c.Year == year && c.Month == month);
            //Excel功能 
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //新建一个xls文件 
            ISheet sheet = hssfworkbook.CreateSheet("市内交通费(" + year + month + ")" + project.ProjectCode); //创建一个sheet 
            sheet.DefaultRowHeight = 200 * 2;
            #region 获取数据

            List<CityTaxiDetail> list = null;
            using (Context ctx = new Context())
            {
                list = (from c in ctx.CityTaxiDetail
                        join d in ctx.CityTaxi
                        on c.CityTaxiId equals d.Id
                        join e in ctx.User
                        on d.UserName equals e.UserName
                        where d.Year == year && d.Month == month && d.ProjectId == projectId
                        orderby d.UserName
                        select new { c, d, e })
                         .ToList()
                         .Select(f => new CityTaxiDetail().Set(f.c).LoadCityTaxi(f.d, f.e)).ToList();
            }
            #endregion
            #region 向sheet中添加数据
            // sheet.DefaultRowHeight = 200 * 20;
            string startdate = DateTime.Parse(financialPeriod.StartDate).ToString("yyyy年MM月dd日");
            string enddate = DateTime.Parse(financialPeriod.EndDate).ToString("yyyy年MM月dd日");
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            cell.SetCellValue("勘察记录表（" + startdate + "-" + enddate + "）" + "项目编号：" + project.ProjectCode);
            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font = hssfworkbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            font.Boldweight = 600;//加粗
            style.SetFont(font);
            cell.CellStyle = style;
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6));

            sheet.SetColumnWidth(1, 20 * 256);
            sheet.SetColumnWidth(2, 20 * 256);
            sheet.SetColumnWidth(3, 20 * 256);
            sheet.SetColumnWidth(4, 30 * 256);
            sheet.SetColumnWidth(5, 10 * 256);
            sheet.SetColumnWidth(6, 10 * 256);


            //添加表头
            //表头样式
            ICellStyle headstyle = hssfworkbook.CreateCellStyle();
            headstyle.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            headstyle.Alignment = HorizontalAlignment.CENTER;//左对齐
            headstyle.VerticalAlignment = VerticalAlignment.CENTER;
            //表头单元格的边框
            headstyle.BorderTop = BorderStyle.THIN;
            headstyle.TopBorderColor = HSSFColor.BLACK.index;
            headstyle.BorderRight = BorderStyle.THIN;
            headstyle.RightBorderColor = HSSFColor.BLACK.index;
            headstyle.BorderBottom = BorderStyle.THIN;
            headstyle.BottomBorderColor = HSSFColor.BLACK.index;
            headstyle.BorderLeft = BorderStyle.THIN;
            headstyle.LeftBorderColor = HSSFColor.BLACK.index;
            //表头的字体
            IFont headfont = hssfworkbook.CreateFont();
            headfont.FontName = "宋体";
            headfont.FontHeightInPoints = 10;//字号
            headfont.Boldweight = 600;
            headstyle.SetFont(headfont);


            row = sheet.CreateRow(1);
            cell = row.CreateCell(0);
            cell.SetCellValue("序号");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(1);
            cell.SetCellValue("姓名");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(2);
            cell.SetCellValue("勘察日期");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(3);
            cell.SetCellValue("出差地点");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(4);
            cell.SetCellValue("勘察站名");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(5);
            cell.SetCellValue("出租车费");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(6);
            cell.SetCellValue("发票数");
            cell.CellStyle = headstyle;


            #region 添加数据
            //数据样式
            ICellStyle datastyle = hssfworkbook.CreateCellStyle();
            datastyle.Alignment = HorizontalAlignment.CENTER;//左对齐
            datastyle.VerticalAlignment = VerticalAlignment.CENTER;
            //数据单元格的边框
            datastyle.BorderTop = BorderStyle.THIN;
            datastyle.TopBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderRight = BorderStyle.THIN;
            datastyle.RightBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderBottom = BorderStyle.THIN;
            datastyle.BottomBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderLeft = BorderStyle.THIN;
            datastyle.LeftBorderColor = HSSFColor.BLACK.index;
            //数据的字体
            IFont datafont = hssfworkbook.CreateFont();
            datafont.FontName = "宋体";
            datafont.FontHeightInPoints = 10;//字号
            datastyle.SetFont(datafont);


            double totalfee = 0;
            int totalcount = 0;
            int j = 2;
            if (list != null && list.Count > 0)
            {
                int i = 1;
                foreach (var item in list)
                {
                    row = sheet.CreateRow(j);
                    cell = row.CreateCell(0);
                    cell.SetCellValue(i);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(1);
                    cell.SetCellValue(item.cityTaxi.user.RealName);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(2);
                    cell.SetCellValue(item.ReconnaissanceDate);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(3);
                    cell.SetCellValue(item.TravelAddress);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(4);
                    cell.SetCellValue(item.ReconnaissanceName);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(5);
                    cell.SetCellValue(double.Parse(item.TaxiFee.ToString()));
                    cell.CellStyle = datastyle;
                    totalfee = totalfee + double.Parse(item.TaxiFee.ToString());
                    cell = row.CreateCell(6);
                    cell.SetCellValue(item.InvoiceCount);
                    cell.CellStyle = datastyle;
                    totalcount = totalcount + item.InvoiceCount;
                    j++;
                    i++;
                }
            }

            row = sheet.CreateRow(j);
            cell = row.CreateCell(0);
            cell.SetCellValue("合计");

            cell.CellStyle = headstyle;
            cell = row.CreateCell(1);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(2);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(3);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(4);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(5);
            cell.SetCellValue(totalfee);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(6);
            cell.SetCellValue(totalcount);
            cell.CellStyle = headstyle;
            #endregion

            row = sheet.CreateRow(j + 1);
            cell = row.CreateCell(0);
            cell.SetCellValue("说明：本表作为每月报销差旅费附件。");
            style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            font = hssfworkbook.CreateFont();
            font.Color = HSSFColor.RED.index;
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            font.Boldweight = 600;//加粗
            style.SetFont(font);
            cell.CellStyle = style;

            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6));
            sheet.AddMergedRegion(new CellRangeAddress(j + 1, j + 1, 0, 6));


            sheet.CreateFreezePane(2, 2);//列,行// 从第几行和第几列开始冻结


            #endregion
            MemoryStream filestream = new MemoryStream(); //内存文件流(应该可以写成普通的文件流) 
            hssfworkbook.Write(filestream); //把文件读到内存流里面 
            // return new FileContentResult(filestream.GetBuffer(), "application/vnd.ms-excel"); 

            HttpContext.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Response.ContentEncoding = Encoding.UTF8;
            HttpContext.Response.Charset = "";
            HttpContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("市内交通费" + year + month + "(" + project.ProjectCode + ")汇总" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", Encoding.UTF8));
            HttpContext.Response.BinaryWrite(filestream.GetBuffer());
        }

        #endregion
        #region 住宿费
        public ActionResult HotelFeeSummary(int page = 0)
        {
            int count = 0;
            List<HotelFee> hotelFeeList = null;
            using (Context ctx = new Context())
            {
                string countSql = @"select ROW_NUMBER() OVER(ORDER BY a.Year desc,a.Month desc) as rownumber,
                             a.*,b.ProjectCode,b.ProjectName from (select Year,Month,StartDate,EndDate,ProjectId,
                             sum(TotalDays) as TotalDays,SUM(TotalFee) as TotalFee,SUM(invoiceCount) as invoiceCount from dbo.T_HotelFee
                            where Status=1
                            group by Year,Month,StartDate,EndDate,ProjectId) a
                            inner join dbo.T_Project b on a.ProjectId=b.Id";
                var dataCount = ctx.ExcuteSQL(countSql);
                count = dataCount != null ? dataCount.Rows.Count : 0;
            }

            using (Context ctx = new Context())
            {
                string sql = @"select top (@pagesize) t.* from (select ROW_NUMBER() OVER(ORDER BY a.Year desc,a.Month desc) as rownumber,
                                 a.*,b.ProjectCode,b.ProjectName from (select Year,Month,StartDate,EndDate,ProjectId,
                                 sum(TotalDays) as TotalDays,SUM(TotalFee) as TotalFee,SUM(invoiceCount) as invoiceCount from dbo.T_HotelFee
                                where Status=1
                                group by Year,Month,StartDate,EndDate,ProjectId) a
                                inner join dbo.T_Project b on a.ProjectId=b.Id) t
                                where t.RowNumber>@pagesize*@page";
                var datatable = ctx.ExcuteSQL(sql,
                         new SqlParameter("@pagesize", 20),
                     new SqlParameter("@page", page)
                    );

                if (datatable != null && datatable.Rows.Count > 0)
                {
                    hotelFeeList = new List<HotelFee>();
                    foreach (DataRow dr in datatable.Rows)
                    {
                        var hotelFee = new HotelFee();
                        hotelFee.Year = dr["Year"].ToString();
                        hotelFee.Month = dr["Month"].ToString();
                        hotelFee.StartDate = dr["StartDate"].ToString();
                        hotelFee.EndDate = dr["EndDate"].ToString();
                        hotelFee.ProjectId = int.Parse(dr["ProjectId"].ToString() == "" ? "0" : dr["ProjectId"].ToString());
                        hotelFee.project = new Project() { ProjectCode = dr["ProjectCode"].ToString(), ProjectName = dr["ProjectName"].ToString() };
                        hotelFee.TotalDays = int.Parse(dr["TotalDays"].ToString() == "" ? "0" : dr["TotalDays"].ToString());
                        hotelFee.TotalFee = decimal.Parse(dr["TotalFee"].ToString() == "" ? "0" : dr["TotalFee"].ToString());
                        hotelFee.InvoiceCount = int.Parse(dr["invoiceCount"].ToString() == "" ? "0" : dr["invoiceCount"].ToString());
                        hotelFeeList.Add(hotelFee);
                    }

                }


            }
            var list = new ItemListBox<HotelFee>(hotelFeeList).BuildPage(
                  count, page, 20, new PageParameter() { Style = "workspace" });
            ViewBag.feelist = list;
            return View();
        }

        public ActionResult HotelFeeSummaryDetail(string year, string month, int projectId = 0)
        {
            ViewBag.project = new Project().Find(projectId.ToString());
            ViewBag.financialPeriod = new FinancialPeriod().Find(c => c.Year == year && c.Month == month);

            using (Context ctx = new Context())
            {
                var data = (from c in ctx.HotelFeeDetail
                            join d in ctx.HotelFee
                            on c.HotelId equals d.Id
                            join e in ctx.User
                           on d.UserName equals e.UserName
                            where d.Year == year && d.Month == month && d.ProjectId == projectId
                            orderby d.UserName
                            select new { c, d, e })
                          .ToList()
                          .Select(f => new HotelFeeDetail().Set(f.c).LoadHotelFee(f.d, f.e)).ToList();
                ViewBag.data = data;

            }

            return View();
        }

        public void HotelFeeExcel(string year, string month, int projectId = 0)
        {
            var project = new Project().Find(projectId.ToString());
            var financialPeriod = new FinancialPeriod().Find(c => c.Year == year && c.Month == month);
            //Excel功能 
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //新建一个xls文件 
            ISheet sheet = hssfworkbook.CreateSheet("住宿费(" + year + month + ")" + project.ProjectCode); //创建一个sheet 
            sheet.DefaultRowHeight = 200 * 2;
            #region 获取数据

            List<HotelFeeDetail> list = null;
            using (Context ctx = new Context())
            {
                list = (from c in ctx.HotelFeeDetail
                        join d in ctx.HotelFee
                        on c.HotelId equals d.Id
                        join e in ctx.User
                        on d.UserName equals e.UserName
                        where d.Year == year && d.Month == month && d.ProjectId == projectId
                        orderby d.UserName
                        select new { c, d, e })
                         .ToList()
                         .Select(f => new HotelFeeDetail().Set(f.c).LoadHotelFee(f.d, f.e)).ToList();
            }
            #endregion
            #region 向sheet中添加数据

            string startdate = DateTime.Parse(financialPeriod.StartDate).ToString("yyyy年MM月dd日");
            string enddate = DateTime.Parse(financialPeriod.EndDate).ToString("yyyy年MM月dd日");
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            cell.SetCellValue("住宿费登记表（" + startdate + "-" + enddate + "）" + "项目编号：" + project.ProjectCode);
            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font = hssfworkbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            font.Boldweight = 600;//加粗
            style.SetFont(font);
            cell.CellStyle = style;
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 9));

            sheet.SetColumnWidth(0, 30 * 256);
            sheet.SetColumnWidth(1, 20 * 256);
            sheet.SetColumnWidth(2, 20 * 256);
            sheet.SetColumnWidth(3, 20 * 256);
            sheet.SetColumnWidth(4, 10 * 256);
            sheet.SetColumnWidth(5, 20 * 256);
            sheet.SetColumnWidth(6, 30 * 256);
            sheet.SetColumnWidth(7, 10 * 256);
            sheet.SetColumnWidth(8, 10 * 256);
            sheet.SetColumnWidth(9, 10 * 256);

            //添加表头
            //表头样式
            ICellStyle headstyle = hssfworkbook.CreateCellStyle();
            headstyle.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            headstyle.Alignment = HorizontalAlignment.CENTER;//左对齐
            headstyle.VerticalAlignment = VerticalAlignment.CENTER;
            //表头单元格的边框
            headstyle.BorderTop = BorderStyle.THIN;
            headstyle.TopBorderColor = HSSFColor.BLACK.index;
            headstyle.BorderRight = BorderStyle.THIN;
            headstyle.RightBorderColor = HSSFColor.BLACK.index;
            headstyle.BorderBottom = BorderStyle.THIN;
            headstyle.BottomBorderColor = HSSFColor.BLACK.index;
            headstyle.BorderLeft = BorderStyle.THIN;
            headstyle.LeftBorderColor = HSSFColor.BLACK.index;
            //表头的字体
            IFont headfont = hssfworkbook.CreateFont();
            headfont.FontName = "宋体";
            headfont.FontHeightInPoints = 10;//字号
            headfont.Boldweight = 600;
            headstyle.SetFont(headfont);


            row = sheet.CreateRow(1);
            cell = row.CreateCell(0);
            cell.SetCellValue("专业所");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(1);
            cell.SetCellValue("姓名");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(2);
            cell.SetCellValue("出差日期");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(3);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(4);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(5);
            cell.SetCellValue("出差地点");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(6);
            cell.SetCellValue("入住酒店");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(7);
            cell.SetCellValue("单价");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(8);
            cell.SetCellValue("出租车费");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(9);
            cell.SetCellValue("发票数");
            cell.CellStyle = headstyle;



            row = sheet.CreateRow(2);
            cell = row.CreateCell(0);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(1);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(2);
            cell.SetCellValue("起");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(3);
            cell.SetCellValue("止");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(4);
            cell.SetCellValue("天数");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(5);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(6);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(7);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(8);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(9);
            cell.CellStyle = headstyle;

            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 2, 4));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 5, 5));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 6, 6));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 7, 7));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 8, 8));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 9, 9));

            #region 添加数据
            //数据样式
            ICellStyle datastyle = hssfworkbook.CreateCellStyle();
            datastyle.Alignment = HorizontalAlignment.CENTER;//左对齐
            datastyle.VerticalAlignment = VerticalAlignment.CENTER;
            //数据单元格的边框
            datastyle.BorderTop = BorderStyle.THIN;
            datastyle.TopBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderRight = BorderStyle.THIN;
            datastyle.RightBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderBottom = BorderStyle.THIN;
            datastyle.BottomBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderLeft = BorderStyle.THIN;
            datastyle.LeftBorderColor = HSSFColor.BLACK.index;
            //数据的字体
            IFont datafont = hssfworkbook.CreateFont();
            datafont.FontName = "宋体";
            datafont.FontHeightInPoints = 10;//字号
            datastyle.SetFont(datafont);


            double totalfee = 0;
            int totalcount = 0;
            int totalDays = 0;
            int j = 3;
            if (list != null && list.Count > 0)
            {
                int i = 1;
                foreach (var item in list)
                {
                    row = sheet.CreateRow(j);
                    cell = row.CreateCell(0);
                    cell.SetCellValue(item.hotelFee.user.ProfessionName);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(1);
                    cell.SetCellValue(item.hotelFee.user.RealName);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(2);
                    cell.SetCellValue(item.CheckInDate);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(3);
                    cell.SetCellValue(item.CheckOutDate);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(4);
                    cell.SetCellValue(item.Days);
                    totalDays = totalDays + item.Days;
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(5);
                    cell.SetCellValue(item.TravelAddress);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(6);
                    cell.SetCellValue(item.HotelName);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(7);
                    cell.SetCellValue(double.Parse(item.Price.ToString()));
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(8);
                    cell.SetCellValue(double.Parse(item.TotalFee.ToString()));
                    cell.CellStyle = datastyle;
                    totalfee = totalfee + double.Parse(item.TotalFee.ToString());
                    cell = row.CreateCell(9);
                    cell.SetCellValue(item.InvoiceCount);
                    cell.CellStyle = datastyle;
                    totalcount = totalcount + item.InvoiceCount;
                    j++;
                    i++;
                }
            }

            row = sheet.CreateRow(j);
            cell = row.CreateCell(0);
            cell.SetCellValue("合计");

            cell.CellStyle = headstyle;
            cell = row.CreateCell(1);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(2);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(3);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(4);
            cell.SetCellValue(totalDays);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(5);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(6);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(7);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(8);
            cell.SetCellValue(totalfee);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(9);
            cell.SetCellValue(totalcount);
            cell.CellStyle = headstyle;
            #endregion

            row = sheet.CreateRow(j + 1);
            cell = row.CreateCell(0);
            cell.SetCellValue("说明：本表作为每月报销差旅费附件。");
            style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            font = hssfworkbook.CreateFont();
            font.Color = HSSFColor.RED.index;
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            font.Boldweight = 600;//加粗
            style.SetFont(font);
            cell.CellStyle = style;

            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 9));
            sheet.AddMergedRegion(new CellRangeAddress(j + 1, j + 1, 0, 9));


            sheet.CreateFreezePane(2, 3);//列,行// 从第几行和第几列开始冻结


            #endregion
            MemoryStream filestream = new MemoryStream(); //内存文件流(应该可以写成普通的文件流) 
            hssfworkbook.Write(filestream); //把文件读到内存流里面 
            // return new FileContentResult(filestream.GetBuffer(), "application/vnd.ms-excel"); 

            HttpContext.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Response.ContentEncoding = Encoding.UTF8;
            HttpContext.Response.Charset = "";
            HttpContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("住宿费" + year + month + "(" + project.ProjectCode + ")汇总" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", Encoding.UTF8));
            HttpContext.Response.BinaryWrite(filestream.GetBuffer());
        }

        #endregion

        #region 在途费
        public ActionResult LongFeeSummary(int page = 0)
        {
            int count = 0;
            List<LongHaul> longFeeList = null;
            using (Context ctx = new Context())
            {
                string countSql = @"select ROW_NUMBER() OVER(ORDER BY a.Year desc,a.Month desc) as rownumber,
                             a.*,b.ProjectCode,b.ProjectName from (select Year,Month,StartDate,EndDate,ProjectId,
                             sum(TotalDays) as TotalDays,SUM(TotalFee) as TotalFee,SUM(invoiceCount) as invoiceCount from dbo.T_LongHaul
                            where Status=1
                            group by Year,Month,StartDate,EndDate,ProjectId) a
                            inner join dbo.T_Project b on a.ProjectId=b.Id";
                var dataCount = ctx.ExcuteSQL(countSql);
                count = dataCount != null ? dataCount.Rows.Count : 0;
            }

            using (Context ctx = new Context())
            {
                string sql = @"select top (@pagesize) t.* from (select ROW_NUMBER() OVER(ORDER BY a.Year desc,a.Month desc) as rownumber,
                                 a.*,b.ProjectCode,b.ProjectName from (select Year,Month,StartDate,EndDate,ProjectId,
                                 sum(TotalDays) as TotalDays,SUM(TotalFee) as TotalFee,SUM(invoiceCount) as invoiceCount from dbo.T_LongHaul
                                where Status=1
                                group by Year,Month,StartDate,EndDate,ProjectId) a
                                inner join dbo.T_Project b on a.ProjectId=b.Id) t
                                where t.RowNumber>@pagesize*@page";
                var datatable = ctx.ExcuteSQL(sql,
                         new SqlParameter("@pagesize", 20),
                     new SqlParameter("@page", page)
                    );

                if (datatable != null && datatable.Rows.Count > 0)
                {
                    longFeeList = new List<LongHaul>();
                    foreach (DataRow dr in datatable.Rows)
                    {
                        var longFee = new LongHaul();
                        longFee.Year = dr["Year"].ToString();
                        longFee.Month = dr["Month"].ToString();
                        longFee.StartDate = dr["StartDate"].ToString();
                        longFee.EndDate = dr["EndDate"].ToString();
                        longFee.ProjectId = int.Parse(dr["ProjectId"].ToString() == "" ? "0" : dr["ProjectId"].ToString());
                        longFee.project = new Project() { ProjectCode = dr["ProjectCode"].ToString(), ProjectName = dr["ProjectName"].ToString() };
                        longFee.TotalDays = int.Parse(dr["TotalDays"].ToString() == "" ? "0" : dr["TotalDays"].ToString());
                        longFee.TotalFee = decimal.Parse(dr["TotalFee"].ToString() == "" ? "0" : dr["TotalFee"].ToString());
                        longFee.InvoiceCount = int.Parse(dr["invoiceCount"].ToString() == "" ? "0" : dr["invoiceCount"].ToString());
                        longFeeList.Add(longFee);
                    }

                }


            }
            var list = new ItemListBox<LongHaul>(longFeeList).BuildPage(
                  count, page, 20, new PageParameter() { Style = "workspace" });
            ViewBag.feelist = list;
            return View();
        }

        public ActionResult LongFeeSummaryDetail(string year, string month, int projectId = 0)
        {
            ViewBag.project = new Project().Find(projectId.ToString());
            ViewBag.financialPeriod = new FinancialPeriod().Find(c => c.Year == year && c.Month == month);

            using (Context ctx = new Context())
            {
                var data = (from c in ctx.LongHaulDetail
                            join d in ctx.LongHaul
                            on c.LongHaulId equals d.Id
                            join e in ctx.User
                           on d.UserName equals e.UserName
                            where d.Year == year && d.Month == month && d.ProjectId == projectId
                            orderby d.UserName
                            select new { c, d, e })
                          .ToList()
                          .Select(f => new LongHaulDetail().Set(f.c).LoadLongFee(f.d, f.e)).ToList();
                ViewBag.data = data;
                ViewBag.dataStr = GetTboby(data);
                ViewBag.longfee = new LongHaul().Find(c => c.Year == year && c.Month == month && c.ProjectId == projectId);
            }

            return View();
        }

        public string GetTboby(List<LongHaulDetail> detailList)
        {
            StringBuilder sb = new StringBuilder();
            var tempList = new List<LongHaulDetail>();
            if (detailList != null && detailList.Count > 0)
            {

                int j = 1;
                foreach (var item in detailList)
                {
                    var temp = item;
                    string[] des = item.LongHaulDescription.Split('#');
                    var longdtoList = new List<LongHaulDTO>();
                    foreach (var str in des)
                    {
                        if (str != "")
                        {
                            var dto = new LongHaulDTO();
                            var dtos = str.Split('|');

                            dto.BusDate = dtos[2];
                            dto.EndAddress = dtos[1];
                            dto.Fee = decimal.Parse(dtos[4] == "" ? "0" : dtos[4]);
                            dto.InvoiceCount = int.Parse(dtos[5] == "" ? "0" : dtos[5]);
                            dto.StartAddress = dtos[0];
                            dto.Transport = dtos[3];
                            longdtoList.Add(dto);
                        }
                    }
                    temp.tempId = j;
                    temp.dtoList = longdtoList;
                    tempList.Add(temp);
                    j++;
                }

            }

            return ConvertToStr(tempList);
        }

        public string ConvertToStr(List<LongHaulDetail> detailList)
        {
            string userName = System.Web.HttpContext.Current.User.Identity.Name;
            string realName = new User().Find(c => c.UserName == userName).RealName;
            StringBuilder sb = new StringBuilder();
            int days = 0;
            decimal totalfee = 0;
            int totalinvoicecount = 0;
            if (detailList != null && detailList.Any())
            {
                int i = 1;
                foreach (var item in detailList)
                {
                    totalfee = totalfee + item.TotalFee;
                    days = days + item.Days;
                    totalinvoicecount = totalinvoicecount + item.InvoiceCount;
                    if (item.dtoList != null && item.dtoList.Count > 0)
                    {
                        int rowcount = item.dtoList.Count;
                        int j = 0;
                        foreach (var dto in item.dtoList)
                        {
                            if (j == 0)
                            {
                                sb.Append("<tr bgcolor=\"#FFFFFF\" align=\"center\">");
                                sb.Append("<td>" + i + "</td>");
                                sb.Append("<td width=\"10%\" align=\"center\">" + item.longHaul.user.RealName + "</td>");
                                sb.Append("<td width=\"15%\" align=\"center\">" + dto.StartAddress + "</td>");
                                sb.Append("<td width=\"15%\" align=\"center\">" + dto.EndAddress + "</td>");
                                sb.Append("<td width=\"15%\" align=\"center\">" + dto.BusDate + "</td>");
                                sb.Append("<td width=\"10%\" align=\"center\" rowspan=\"" + rowcount + "\">" + item.Days + "</td>");
                                sb.Append("<td width=\"10%\" align=\"center\">" + dto.Transport + "</td>");
                                sb.Append("<td width=\"10%\" align=\"center\">" + dto.Fee + "</td>");
                                sb.Append("<td width=\"10%\" align=\"center\">" + dto.InvoiceCount + "</td>");
                                sb.Append("</tr>");
                            }
                            else
                            {
                                sb.Append("<tr bgcolor=\"#FFFFFF\" align=\"center\">");
                                sb.Append("<td>" + i + "</td>");
                                sb.Append("<td width=\"10%\" align=\"center\">" + item.longHaul.user.RealName + "</td>");
                                sb.Append("<td width=\"15%\" align=\"center\">" + dto.StartAddress + "</td>");
                                sb.Append("<td width=\"15%\" align=\"center\">" + dto.EndAddress + "</td>");
                                sb.Append("<td width=\"15%\" align=\"center\">" + dto.BusDate + "</td>");
                                sb.Append("<td width=\"10%\" align=\"center\">" + dto.Transport + "</td>");
                                sb.Append("<td width=\"10%\" align=\"center\">" + dto.Fee + "</td>");
                                sb.Append("<td width=\"10%\" align=\"center\">" + dto.InvoiceCount + "</td>");

                                sb.Append("</tr>");
                            }
                            i++;
                            j++;
                        }
                    }

                }
            }

            return sb.ToString();
        }

        public void LongFeeExcel(string year, string month, int projectId = 0)
        {
            var project = new Project().Find(projectId.ToString());
            var financialPeriod = new FinancialPeriod().Find(c => c.Year == year && c.Month == month);
            //Excel功能 
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //新建一个xls文件 
            ISheet sheet = hssfworkbook.CreateSheet("在途费(" + year + month + ")" + project.ProjectCode); //创建一个sheet 
            sheet.DefaultRowHeight = 200 * 2;
            #region 获取数据

            List<LongHaulDetail> list = null;
            using (Context ctx = new Context())
            {
                list = (from c in ctx.LongHaulDetail
                        join d in ctx.LongHaul
                        on c.LongHaulId equals d.Id
                        join e in ctx.User
                       on d.UserName equals e.UserName
                        where d.Year == year && d.Month == month && d.ProjectId == projectId
                        orderby d.UserName
                        select new { c, d, e })
                          .ToList()
                          .Select(f => new LongHaulDetail().Set(f.c).LoadLongFee(f.d, f.e)).ToList();
            }

            var tempList = new List<LongHaulDetail>();
            if (list != null && list.Count > 0)
            {

                int m = 1;
                foreach (var item in list)
                {
                    var temp = item;
                    string[] des = item.LongHaulDescription.Split('#');
                    var longdtoList = new List<LongHaulDTO>();
                    foreach (var str in des)
                    {
                        if (str != "")
                        {
                            var dto = new LongHaulDTO();
                            var dtos = str.Split('|');

                            dto.BusDate = dtos[2];
                            dto.EndAddress = dtos[1];
                            dto.Fee = decimal.Parse(dtos[4] == "" ? "0" : dtos[4]);
                            dto.InvoiceCount = int.Parse(dtos[5] == "" ? "0" : dtos[5]);
                            dto.StartAddress = dtos[0];
                            dto.Transport = dtos[3];
                            longdtoList.Add(dto);
                        }
                    }
                    temp.tempId = m;
                    temp.dtoList = longdtoList;
                    tempList.Add(temp);
                    m++;
                }

            }

            #endregion
            #region 向sheet中添加数据

            string startdate = DateTime.Parse(financialPeriod.StartDate).ToString("yyyy年MM月dd日");
            string enddate = DateTime.Parse(financialPeriod.EndDate).ToString("yyyy年MM月dd日");
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            cell.SetCellValue("在途费用记录（" + startdate + "-" + enddate + "）" + "项目编号：" + project.ProjectCode);
            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font = hssfworkbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            font.Boldweight = 600;//加粗
            style.SetFont(font);
            cell.CellStyle = style;
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 8));

            sheet.SetColumnWidth(0, 8 * 256);
            sheet.SetColumnWidth(1, 10 * 256);
            sheet.SetColumnWidth(2, 10 * 256);
            sheet.SetColumnWidth(3, 10 * 256);
            sheet.SetColumnWidth(4, 20 * 256);
            sheet.SetColumnWidth(5, 10 * 256);
            sheet.SetColumnWidth(6, 10 * 256);
            sheet.SetColumnWidth(7, 10 * 256);
            sheet.SetColumnWidth(8, 10 * 256);

            //添加表头
            //表头样式
            ICellStyle headstyle = hssfworkbook.CreateCellStyle();
            headstyle.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            headstyle.Alignment = HorizontalAlignment.CENTER;//左对齐
            headstyle.VerticalAlignment = VerticalAlignment.CENTER;
            //表头单元格的边框
            headstyle.BorderTop = BorderStyle.THIN;
            headstyle.TopBorderColor = HSSFColor.BLACK.index;
            headstyle.BorderRight = BorderStyle.THIN;
            headstyle.RightBorderColor = HSSFColor.BLACK.index;
            headstyle.BorderBottom = BorderStyle.THIN;
            headstyle.BottomBorderColor = HSSFColor.BLACK.index;
            headstyle.BorderLeft = BorderStyle.THIN;
            headstyle.LeftBorderColor = HSSFColor.BLACK.index;
            //表头的字体
            IFont headfont = hssfworkbook.CreateFont();
            headfont.FontName = "宋体";
            headfont.FontHeightInPoints = 10;//字号
            headfont.Boldweight = 600;
            headstyle.SetFont(headfont);


            row = sheet.CreateRow(1);
            cell = row.CreateCell(0);
            cell.SetCellValue("序号");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(1);
            cell.SetCellValue("姓名");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(2);
            cell.SetCellValue("行程");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(3);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(4);
            cell.SetCellValue("日期");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(5);
            cell.SetCellValue("出差天数");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(6);
            cell.SetCellValue("交通工具");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(7);
            cell.SetCellValue("在途费用");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(8);
            cell.SetCellValue("发票数");
            cell.CellStyle = headstyle;



            row = sheet.CreateRow(2);
            cell = row.CreateCell(0);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(1);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(2);
            cell.SetCellValue("起");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(3);
            cell.SetCellValue("止");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(4);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(5);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(6);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(7);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(8);
            cell.CellStyle = headstyle;

            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 2, 3));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 4, 4));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 5, 5));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 6, 6));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 7, 7));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 8, 8));


            #region 添加数据
            //数据样式
            ICellStyle datastyle = hssfworkbook.CreateCellStyle();
            datastyle.Alignment = HorizontalAlignment.CENTER;//左对齐
            datastyle.VerticalAlignment = VerticalAlignment.CENTER;
            //数据单元格的边框
            datastyle.BorderTop = BorderStyle.THIN;
            datastyle.TopBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderRight = BorderStyle.THIN;
            datastyle.RightBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderBottom = BorderStyle.THIN;
            datastyle.BottomBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderLeft = BorderStyle.THIN;
            datastyle.LeftBorderColor = HSSFColor.BLACK.index;
            //数据的字体
            IFont datafont = hssfworkbook.CreateFont();
            datafont.FontName = "宋体";
            datafont.FontHeightInPoints = 10;//字号
            datastyle.SetFont(datafont);


            double totalfee = 0;
            int totalcount = 0;
            int totalDays = 0;
            int j = 3;
            if (list != null && list.Count > 0)
            {
                int k = 1;
                foreach (var item in list)
                {
                    totalDays = totalDays + item.Days;
                    totalcount = totalcount + item.InvoiceCount;
                    totalfee = totalfee + double.Parse(item.TotalFee.ToString());
                    if (item.dtoList != null && item.dtoList.Count > 0)
                    {
                        int rowcount = item.dtoList.Count;

                        int n = 0;
                        foreach (var dto in item.dtoList)
                        {
                            if (n == 0)
                            {
                                row = sheet.CreateRow(j);
                                cell = row.CreateCell(0);
                                cell.SetCellValue(k);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(1);
                                cell.SetCellValue(item.longHaul.user.RealName);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(2);
                                cell.SetCellValue(dto.StartAddress);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(3);
                                cell.SetCellValue(dto.EndAddress);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(4);
                                cell.SetCellValue(dto.BusDate);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(5);
                                cell.SetCellValue(item.Days);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(6);
                                cell.SetCellValue(dto.Transport);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(7);
                                cell.SetCellValue(double.Parse(dto.Fee.ToString()));
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(8);
                                cell.SetCellValue(dto.InvoiceCount);
                                cell.CellStyle = datastyle;
                            }
                            else
                            {
                                row = sheet.CreateRow(j);
                                cell = row.CreateCell(0);
                                cell.SetCellValue(k);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(1);
                                cell.SetCellValue(item.longHaul.user.RealName);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(2);
                                cell.SetCellValue(dto.StartAddress);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(3);
                                cell.SetCellValue(dto.EndAddress);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(4);
                                cell.SetCellValue(dto.BusDate);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(5);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(6);
                                cell.SetCellValue(dto.Transport);
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(7);
                                cell.SetCellValue(double.Parse(dto.Fee.ToString()));
                                cell.CellStyle = datastyle;

                                cell = row.CreateCell(8);
                                cell.SetCellValue(dto.InvoiceCount);
                                cell.CellStyle = datastyle;
                            }
                            n++;
                            j++;
                            k++;
                        }
                        sheet.AddMergedRegion(new CellRangeAddress(j - rowcount, j - 1, 5, 5));
                    }

                }
            }

            row = sheet.CreateRow(j);
            cell = row.CreateCell(0);
            cell.SetCellValue("合计");

            cell.CellStyle = headstyle;
            cell = row.CreateCell(1);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(2);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(3);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(4);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(5);
            cell.SetCellValue(totalDays);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(6);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(7);
            cell.SetCellValue(totalfee);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(8);
            cell.SetCellValue(totalcount);
            cell.CellStyle = headstyle;
            #endregion

            row = sheet.CreateRow(j + 1);
            cell = row.CreateCell(0);
            cell.SetCellValue("说明：本表作为每月报销差旅费附件。");
            style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            font = hssfworkbook.CreateFont();
            font.Color = HSSFColor.RED.index;
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            font.Boldweight = 600;//加粗
            style.SetFont(font);
            cell.CellStyle = style;

            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 8));
            sheet.AddMergedRegion(new CellRangeAddress(j + 1, j + 1, 0, 8));


            sheet.CreateFreezePane(2, 3);//列,行// 从第几行和第几列开始冻结


            #endregion
            MemoryStream filestream = new MemoryStream(); //内存文件流(应该可以写成普通的文件流) 
            hssfworkbook.Write(filestream); //把文件读到内存流里面 
            // return new FileContentResult(filestream.GetBuffer(), "application/vnd.ms-excel"); 

            HttpContext.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Response.ContentEncoding = Encoding.UTF8;
            HttpContext.Response.Charset = "";
            HttpContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("在途费用" + year + month + "(" + project.ProjectCode + ")汇总" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", Encoding.UTF8));
            HttpContext.Response.BinaryWrite(filestream.GetBuffer());
        }


        #endregion
        #region 汇总
        public ActionResult GetAllSummary(int page = 0)
        {

            int count = 0;
            using (Context ctx = new Context())
            {
                string countSql = @"select distinct  a.*,b.ProjectCode,b.ProjectName from 
                            (select distinct Year,Month, ProjectId from dbo.T_CityTaxi where status=1
                            union
                            select distinct Year,Month, projectid from dbo.T_HotelFee where status=1
                            union
                            select distinct Year,Month, projectid from dbo.T_LongHaul where status=1) a
                            inner join T_Project b on a.ProjectId=b.Id";
                var dataCount = ctx.ExcuteSQL(countSql);
                count = dataCount != null ? dataCount.Rows.Count : 0;
            }

            using (Context ctx = new Context())
            {
                string sql = @"select top (@pagesize) t.* from (select ROW_NUMBER() OVER (ORDER BY a.year,a.month,projectid desc) AS RowNumber, a.*,b.ProjectCode,b.ProjectName from 
                            (select distinct Year,Month,StartDate,EndDate, ProjectId from dbo.T_CityTaxi where status=1
                            union
                            select distinct Year,Month,StartDate,EndDate, projectid from dbo.T_HotelFee where status=1
                            union
                            select distinct Year,Month,StartDate,EndDate, projectid from dbo.T_LongHaul where status=1) a
                            inner join T_Project b on a.ProjectId=b.Id) t
                            where  t.RowNumber>@pagesize*@page";
                var datatable = ctx.ExcuteSQL(sql,
                         new SqlParameter("@pagesize", 20),
                     new SqlParameter("@page", page)
                    );
                List<MonthProjectDTO> monthProjectList = null;
                if (datatable != null && datatable.Rows.Count > 0)
                {
                    monthProjectList = new List<MonthProjectDTO>();
                    foreach (DataRow dr in datatable.Rows)
                    {
                        var monthProject = new MonthProjectDTO();
                        monthProject.Year = dr["Year"].ToString();
                        monthProject.Month = dr["Month"].ToString();
                        monthProject.StartDate = dr["StartDate"].ToString();
                        monthProject.EndDate = dr["EndDate"].ToString();
                        monthProject.ProjectId = dr["ProjectId"].ToString();
                        monthProject.ProjectCode = dr["ProjectCode"].ToString();
                        monthProject.ProjectName = dr["ProjectName"].ToString();
                        monthProjectList.Add(monthProject);
                    }
                    var list = new ItemListBox<MonthProjectDTO>(monthProjectList).BuildPage(
                   count, page, 20, new PageParameter() { Style = "workspace" });
                    ViewBag.monthProjects = list;
                }


            }
            return View();
        }
        public ActionResult GetSummaryDetail(string year, string month, int projectId = 0)
        {
            ViewBag.financialPeriod = new FinancialPeriod().Find(c => c.Year == year && c.Month == month);
            List<User> users = null;
            using (Context ctx = new Context())
            {
                string sql = @"select distinct UserName from dbo.T_CityTaxi where Year=@year and Month=@month and ProjectId=@projectid and status=1
                        union
                        select distinct UserName from dbo.T_HotelFee where Year=@year and Month=@month and ProjectId=@projectid and status=1
                        union
                        select distinct UserName from dbo.T_LongHaul where Year=@year and Month=@month and ProjectId=@projectid and status=1";
                var datatable = ctx.ExcuteSQL(sql,
                       new SqlParameter("@year", year),
                   new SqlParameter("@month", month),
                   new SqlParameter("@projectId", projectId)
                  );
                if (datatable != null && datatable.Rows.Count > 0)
                {
                    users = new List<User>();
                    foreach (DataRow dr in datatable.Rows)
                    {
                        string username = dr["UserName"].ToString();
                        var user = new User().Find(c => c.UserName == username);

                        users.Add(user);
                    }
                }
            }
            if (users != null && users.Count > 0)
            {
                int totalDays = 0;
                decimal ttotalmoney = 0;
                var dtoList = new List<SummaryDetailDTO>();
                foreach (var item in users)
                {
                    var dto = new SummaryDetailDTO();
                    string username = item.UserName;
                    var taxiFee = new CityTaxi().Find(c => c.Year == year && c.Month == month && c.ProjectId == projectId && c.UserName == username && c.Status == 1);
                    var hotelFee = new HotelFee().Find(c => c.Year == year && c.Month == month && c.ProjectId == projectId && c.UserName == username && c.Status == 1);
                    var longFee = new LongHaul().Find(c => c.Year == year && c.Month == month && c.ProjectId == projectId && c.UserName == username && c.Status == 1);
                    dto.userName = item.RealName;
                    dto.cityTaxi = taxiFee;
                    dto.hotelFee = hotelFee;
                    dto.longFee = longFee;
                    

                    decimal taxiMoney = taxiFee == null ? 0 : taxiFee.TaxiFee;
                    int taxiInvoice = taxiFee == null ? 0 : taxiFee.InvoiceCount;

                    decimal hotelMonty = hotelFee == null ? 0 : hotelFee.TotalFee;
                    int hotelInvoice = hotelFee == null ? 0 : hotelFee.InvoiceCount;

                    decimal longMonty = longFee == null ? 0 : longFee.TotalFee;
                    int longInvoice = longFee == null ? 0 : longFee.InvoiceCount;
                    decimal allowanceMoney=longFee == null ? 0 : longFee.AllowanceMoney;

                    int totalInvoice = taxiInvoice + hotelInvoice + longInvoice;
                    decimal totalMoney = taxiMoney + hotelMonty + longMonty;

                    int days = longFee == null ? 0 : longFee.TotalDays;
                    totalDays = totalDays + days;
                    dto.totalCount = totalInvoice;
                    dto.totalFee = totalMoney;
                    dto.lasttotalFee = allowanceMoney + totalMoney;

                    ttotalmoney = ttotalmoney + allowanceMoney + totalMoney;
                    dto.totalDays = days;
                    dtoList.Add(dto);
                }
                ViewBag.data = dtoList;
                ViewBag.totalmoney = ttotalmoney;
                ViewBag.totalDays = totalDays;
            }


            var project = new Project().Find(projectId.ToString());

            ViewBag.project = project;

            return View();
        }

        public void SummaryExcel(string year, string month, int projectId = 0)
        {
            var project = new Project().Find(projectId.ToString());
            var financialPeriod = new FinancialPeriod().Find(c => c.Year == year && c.Month == month);
            //Excel功能 
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //新建一个xls文件 
            ISheet sheet = hssfworkbook.CreateSheet("差旅费汇总(" + year + month + ")" + project.ProjectCode); //创建一个sheet 
            sheet.DefaultRowHeight = 200 * 2;
            #region 取数据
            List<User> users = null;
            List<SummaryDetailDTO> dtoList = null;
            using (Context ctx = new Context())
            {
                string sql = @"select distinct UserName from dbo.T_CityTaxi where Year=@year and Month=@month and ProjectId=@projectid and status=1
                        union
                        select distinct UserName from dbo.T_HotelFee where Year=@year and Month=@month and ProjectId=@projectid and status=1
                        union
                        select distinct UserName from dbo.T_LongHaul where Year=@year and Month=@month and ProjectId=@projectid and status=1";
                var datatable = ctx.ExcuteSQL(sql,
                       new SqlParameter("@year", year),
                   new SqlParameter("@month", month),
                   new SqlParameter("@projectId", projectId)
                  );
                if (datatable != null && datatable.Rows.Count > 0)
                {
                    users = new List<User>();
                    foreach (DataRow dr in datatable.Rows)
                    {
                        string username = dr["UserName"].ToString();
                        var user = new User().Find(c => c.UserName == username);

                        users.Add(user);
                    }
                }
            }
            int totalDays = 0;
            decimal totalmoney = 0;
            if (users != null && users.Count > 0)
            {
               
                dtoList = new List<SummaryDetailDTO>();
                foreach (var item in users)
                {
                    var dto = new SummaryDetailDTO();
                    string username = item.UserName;
                    var taxiFee = new CityTaxi().Find(c => c.Year == year && c.Month == month && c.ProjectId == projectId && c.UserName == username && c.Status == 1);
                    var hotelFee = new HotelFee().Find(c => c.Year == year && c.Month == month && c.ProjectId == projectId && c.UserName == username && c.Status == 1);
                    var longFee = new LongHaul().Find(c => c.Year == year && c.Month == month && c.ProjectId == projectId && c.UserName == username && c.Status == 1);
                    dto.userName = item.RealName;
                    dto.cityTaxi = taxiFee;
                    dto.hotelFee = hotelFee;
                    dto.longFee = longFee;

                    decimal taxiMoney = taxiFee == null ? 0 : taxiFee.TaxiFee;
                    int taxiInvoice = taxiFee == null ? 0 : taxiFee.InvoiceCount;

                    decimal hotelMonty = hotelFee == null ? 0 : hotelFee.TotalFee;
                    int hotelInvoice = hotelFee == null ? 0 : hotelFee.InvoiceCount;

                    decimal longMonty = longFee == null ? 0 : longFee.TotalFee;
                    int longInvoice = longFee == null ? 0 : longFee.InvoiceCount;
                    decimal allowanceMoney = longFee == null ? 0 : longFee.AllowanceMoney;

                    int totalInvoice = taxiInvoice + hotelInvoice + longInvoice;
                    decimal totalMoney = taxiMoney + hotelMonty + longMonty;

                    int days = longFee == null ? 0 : longFee.TotalDays;
                    totalDays = totalDays + days;
                    dto.totalCount = totalInvoice;
                    dto.totalFee = totalMoney;
                    dto.lasttotalFee = allowanceMoney + totalMoney;
                    totalmoney = totalmoney + totalMoney + allowanceMoney;
                    dto.totalDays = days;
                    dtoList.Add(dto);
                }
            }
            #endregion
            #region 向sheet中添加数据
            string startdate = DateTime.Parse(financialPeriod.StartDate).ToString("yyyy年MM月dd日");
            string enddate = DateTime.Parse(financialPeriod.EndDate).ToString("yyyy年MM月dd日");
            IRow row = sheet.CreateRow(0);
            row.Height = 300*2;
            ICell cell = row.CreateCell(0);
            cell.SetCellValue(project.ProjectName + "项目组差旅费汇总表(" + year + "年" + month + "月)");
            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font = hssfworkbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 16;
            font.Boldweight = 600;//加粗
            style.SetFont(font);
            cell.CellStyle = style;
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 22));

            sheet.SetColumnWidth(0, 8 * 256);
            sheet.SetColumnWidth(1, 10 * 256);
            sheet.SetColumnWidth(2, 8 * 256);
            sheet.SetColumnWidth(3, 8 * 256);
            sheet.SetColumnWidth(4, 8 * 256);
            sheet.SetColumnWidth(5, 8 * 256);
            sheet.SetColumnWidth(6, 8 * 256);
            sheet.SetColumnWidth(7, 8 * 256);
            sheet.SetColumnWidth(8, 8 * 256);
            sheet.SetColumnWidth(9, 8 * 256);
            sheet.SetColumnWidth(10, 8 * 256);
            sheet.SetColumnWidth(11, 8 * 256);
            sheet.SetColumnWidth(12, 8 * 256);
            sheet.SetColumnWidth(13, 8 * 256);
            sheet.SetColumnWidth(14, 8 * 256);
            sheet.SetColumnWidth(15, 8 * 256);
            sheet.SetColumnWidth(16, 8 * 256);
            sheet.SetColumnWidth(17, 8 * 256);
            sheet.SetColumnWidth(18, 8 * 256);
            sheet.SetColumnWidth(19, 8 * 256);
            sheet.SetColumnWidth(20, 8 * 256);
            sheet.SetColumnWidth(21, 8 * 256);
            sheet.SetColumnWidth(22, 15 * 256);

            //添加表头
            //表头样式
            ICellStyle headstyle = hssfworkbook.CreateCellStyle();
            headstyle.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            headstyle.Alignment = HorizontalAlignment.CENTER;//左对齐
            headstyle.VerticalAlignment = VerticalAlignment.CENTER;
            headstyle.WrapText = true;
            //表头单元格的边框
            headstyle.BorderTop = BorderStyle.THIN;
            headstyle.TopBorderColor = HSSFColor.BLACK.index;
            headstyle.BorderRight = BorderStyle.THIN;
            headstyle.RightBorderColor = HSSFColor.BLACK.index;
            headstyle.BorderBottom = BorderStyle.THIN;
            headstyle.BottomBorderColor = HSSFColor.BLACK.index;
            headstyle.BorderLeft = BorderStyle.THIN;
            headstyle.LeftBorderColor = HSSFColor.BLACK.index;
            //表头的字体
            IFont headfont = hssfworkbook.CreateFont();
            headfont.FontName = "宋体";
            headfont.FontHeightInPoints = 10;//字号
            headfont.Boldweight = 600;
            headstyle.SetFont(headfont);

            #region 表头

            ICellStyle headstyle1 = hssfworkbook.CreateCellStyle();
            headstyle1.Alignment = HorizontalAlignment.LEFT;//左对齐
            headstyle1.VerticalAlignment = VerticalAlignment.CENTER;
            headstyle1.WrapText = true;
             
            //表头的字体
            IFont headfont1 = hssfworkbook.CreateFont();
            headfont1.FontName = "宋体";
            headfont1.FontHeightInPoints = 12;//字号
            headfont1.Boldweight = 600;
            headstyle1.SetFont(headfont1);

            row = sheet.CreateRow(1);
            row.Height = 250 * 2;
            cell = row.CreateCell(0);
            cell.SetCellValue("报销人:");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(1);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(2);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(3);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(4);
            cell.SetCellValue("部门：");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(5);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(6);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(7);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(8);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(9);
            cell.SetCellValue("总人天数:" + totalDays);
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(10);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(11);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(12);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(13);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(14);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(15);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(16);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(17);
            cell.SetCellValue("差旅费总计："+totalmoney.ToString());
           // headstyle.Alignment = HorizontalAlignment.LEFT;
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(18);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(19);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(20);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(21);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            cell = row.CreateCell(22);
            cell.SetCellValue("");
            cell.CellStyle = headstyle1;
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 17, 22));
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 9, 16));
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 3));
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 4, 8));


            
            row = sheet.CreateRow(2);
            cell = row.CreateCell(0);
            cell.SetCellValue("序号");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(1);
            cell.SetCellValue("姓名");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(2);
            cell.SetCellValue("市内交通费");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(3);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(4);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(5);
            cell.SetCellValue("在途费用(不含机票)");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(6);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(7);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(8);
            cell.SetCellValue("公务杂费");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(9);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(10);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(11);
            cell.SetCellValue("住宿费");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(12);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(13);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(14);
            cell.SetCellValue("汽油费");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(15);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(16);
            cell.SetCellValue("");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(17);
            cell.SetCellValue("发票数");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(18);
            cell.SetCellValue("小计");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(19);
            cell.SetCellValue("出差伙食补助");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(20);
            cell.SetCellValue("合计");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(21);
            cell.SetCellValue("报销单号");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(22);
            cell.SetCellValue("项目编号");
            cell.CellStyle = headstyle;



            row = sheet.CreateRow(3);
            cell = row.CreateCell(0);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(1);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(2);
            cell.SetCellValue("金额");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(3);
            cell.SetCellValue("发票数");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(4);
            cell.SetCellValue("小计");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(5);
            cell.SetCellValue("金额");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(6);
            cell.SetCellValue("发票数");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(7);
            cell.SetCellValue("小计");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(8);
            cell.SetCellValue("金额");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(9);
            cell.SetCellValue("发票数");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(10);
            cell.SetCellValue("小计");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(11);
            cell.SetCellValue("金额");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(12);
            cell.SetCellValue("发票数");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(13);
            cell.SetCellValue("小计");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(14);
            cell.SetCellValue("金额");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(15);
            cell.SetCellValue("发票数");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(16);
            cell.SetCellValue("小计");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(17);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(18);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(19);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(20);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(21);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(22);
            cell.CellStyle = headstyle;


            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 1, 1));
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 17, 17));
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 18, 18));
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 19, 19));
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 20, 20));
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 21, 21));
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 22, 22));

            sheet.AddMergedRegion(new CellRangeAddress(2, 2, 2, 4));
            sheet.AddMergedRegion(new CellRangeAddress(2, 2, 5, 7));
            sheet.AddMergedRegion(new CellRangeAddress(2, 2, 8, 10));
            sheet.AddMergedRegion(new CellRangeAddress(2, 2, 11, 13));
            sheet.AddMergedRegion(new CellRangeAddress(2, 2, 14, 16));

            #endregion
            #region 表身数据
            //数据样式
            ICellStyle datastyle = hssfworkbook.CreateCellStyle();
            datastyle.Alignment = HorizontalAlignment.CENTER;//左对齐
            datastyle.VerticalAlignment = VerticalAlignment.CENTER;
            //数据单元格的边框
            datastyle.BorderTop = BorderStyle.THIN;
            datastyle.TopBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderRight = BorderStyle.THIN;
            datastyle.RightBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderBottom = BorderStyle.THIN;
            datastyle.BottomBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderLeft = BorderStyle.THIN;
            datastyle.LeftBorderColor = HSSFColor.BLACK.index;
            //数据的字体
            IFont datafont = hssfworkbook.CreateFont();
            datafont.FontName = "宋体";
            datafont.FontHeightInPoints = 10;//字号
            datastyle.SetFont(datafont);
            int j = 4;
            double ttaxifee = 0;
            int ttaxicount = 0;
            double thotelfee = 0;
            int thotelcount = 0;
            double tlongfee = 0;
            int tlongcount = 0;
            double tallowance = 0;
            double tfee = 0;
            int tcount = 0;
            double tlastfee = 0;

            if (dtoList != null && dtoList.Count > 0)
            {
                int i = 1;
                foreach (var item in dtoList)
                {
                    decimal taxiMoney = item.cityTaxi == null ? 0 : item.cityTaxi.TaxiFee;
                    int taxiInvoice = item.cityTaxi == null ? 0 : item.cityTaxi.InvoiceCount;
                    ttaxifee = ttaxifee + double.Parse(taxiMoney.ToString());
                    ttaxicount = ttaxicount + taxiInvoice;
                    decimal hotelMonty = item.hotelFee == null ? 0 : item.hotelFee.TotalFee;
                    int hotelInvoice = item.hotelFee == null ? 0 : item.hotelFee.InvoiceCount;
                    thotelfee = thotelfee + double.Parse(hotelMonty.ToString());
                    thotelcount = thotelcount + hotelInvoice;
                    decimal longMonty = item.longFee == null ? 0 : item.longFee.TotalFee;
                    int longInvoice = item.longFee == null ? 0 : item.longFee.InvoiceCount;
                    decimal allowanceMoney=item.longFee == null ? 0 : item.longFee.AllowanceMoney;

                    tlongfee = tlongfee + double.Parse(longMonty.ToString());
                    tlongcount = tlongcount + longInvoice;
                    tallowance = tallowance +  double.Parse(allowanceMoney.ToString());

                    tfee = tfee + double.Parse(item.totalFee.ToString());
                    tcount = tcount + item.totalCount;
                    tlastfee = tfee + double.Parse(allowanceMoney.ToString());

                    row = sheet.CreateRow(j);
                    cell = row.CreateCell(0);
                    cell.SetCellValue(i);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(1);
                    cell.SetCellValue(item.userName);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(2);
                    cell.SetCellValue(double.Parse(taxiMoney.ToString()));
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(3);
                    cell.SetCellValue(taxiInvoice);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(4);
                    cell.SetCellValue(double.Parse(taxiMoney.ToString()));
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(5);
                    cell.SetCellValue(double.Parse(longMonty.ToString()));
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(6);
                    cell.SetCellValue(longInvoice);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(7);
                    cell.SetCellValue(double.Parse(longMonty.ToString()));
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(8);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(9);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(10);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(11);
                    cell.SetCellValue(double.Parse(hotelMonty.ToString()));
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(12);
                    cell.SetCellValue(hotelInvoice);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(13);
                    cell.SetCellValue(double.Parse(hotelMonty.ToString()));
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(14);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(15);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(16);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(17);
                    cell.SetCellValue(item.totalCount);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(18);
                    cell.SetCellValue(double.Parse(item.totalFee.ToString()));
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(19);
                    cell.SetCellValue(double.Parse(allowanceMoney.ToString()));
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(20);
                    cell.SetCellValue(double.Parse(item.totalFee.ToString()));
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(21);
                    cell.CellStyle = datastyle;
                    cell = row.CreateCell(22);
                    if (i == 1)
                    {
                        cell.SetCellValue(project.ProjectCode);
                    }

                    cell.CellStyle = datastyle;

                    i++;
                    j++;
                }
                sheet.AddMergedRegion(new CellRangeAddress(j-dtoList.Count, j-1, 22, 22));
            }
            #endregion
            #region 表尾
            row = sheet.CreateRow(j);
            cell = row.CreateCell(0);
            cell.SetCellValue("合计");
            cell.CellStyle = headstyle;
            cell = row.CreateCell(1);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(2);
            cell.SetCellValue(ttaxifee);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(3);
            cell.SetCellValue(ttaxicount);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(4);
            cell.SetCellValue(ttaxifee);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(5);
            cell.SetCellValue(tlongfee);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(6);
            cell.SetCellValue(tlongcount);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(7);
            cell.SetCellValue(tlongfee);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(8);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(9);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(10);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(11);
            cell.SetCellValue(thotelfee);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(12);
            cell.SetCellValue(thotelcount);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(13);
            cell.SetCellValue(thotelfee);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(14);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(15);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(16);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(17);
            cell.SetCellValue(tcount);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(18);
            cell.SetCellValue(tfee);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(19);
            cell.SetCellValue(tallowance);
            cell.CellStyle = headstyle;//tallowance
            cell = row.CreateCell(20);
            cell.SetCellValue(tlastfee);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(21);
            cell.CellStyle = headstyle;
            cell = row.CreateCell(22);
            cell.CellStyle = headstyle;

            row = sheet.CreateRow(j + 1);
            cell = row.CreateCell(0);
            cell.SetCellValue("说明：本表作为每月报销差旅费附件。");
            style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            font = hssfworkbook.CreateFont();
            font.Color = HSSFColor.RED.index;
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            font.Boldweight = 600;//加粗
            style.SetFont(font);
            cell.CellStyle = style;

            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 22));
            sheet.AddMergedRegion(new CellRangeAddress(j + 1, j + 1, 0, 22));

            


            sheet.CreateFreezePane(2, 4);//列,行// 从第几行和第几列开始冻结


            #endregion
            MemoryStream filestream = new MemoryStream(); //内存文件流(应该可以写成普通的文件流) 
            hssfworkbook.Write(filestream); //把文件读到内存流里面 
            // return new FileContentResult(filestream.GetBuffer(), "application/vnd.ms-excel"); 

            HttpContext.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Response.ContentEncoding = Encoding.UTF8;
            HttpContext.Response.Charset = "";
            HttpContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("差旅费汇总" + year + month + "(" + project.ProjectCode + ")" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", Encoding.UTF8));
            HttpContext.Response.BinaryWrite(filestream.GetBuffer());
            #endregion

        }

        #endregion

    }
}
